﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using uMES.LeanManufacturing.DBUtility;
using System.Data;
using uMES.LeanManufacturing.ParameterDTO;
//using System.Data.OracleClient;
using uMESExternalControl.ToleranceInputLib;
using System.Drawing;
using System.Web.SessionState;
using System.Web;
using CamstarAPI;
using Oracle.ManagedDataAccess.Client;


namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESSecondaryWarehouseBusiness
    {
        uMESCommonBusiness common = new uMESCommonBusiness();
        #region 二级库管理
        #region 001 批次列表
        #region 分页查询
        public uMESPagingDataDTO GetSourceData(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = GetSQL_D(para);

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            return retR;
        }
        #endregion

        #region 不分页查询，用于导出
        public DataTable GetAllDataForOutExcel(Dictionary<string, string> para)
        {
            string strSQL = GetSQL_D(para);

            DataTable DT = OracleHelper.GetDataTable(strSQL);
            return DT;
        }
        #endregion

        #region 组合查询语句
        protected string GetSQL_D(Dictionary<string, string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT sts.containername,sts.productname,sts.description,sts.qty,sts.plannedstartdate,sts.containerid,");
            strQuery.AppendLine("       sts.processno,sts.oprno,sts.plannedcompletiondate,sts.productid,sts.uomid,s.inqty,s.StockQty");
            strQuery.AppendLine("FROM SubmitToStockInfo sts");
            strQuery.AppendLine("LEFT JOIN stockinfo s ON s.containerid = sts.containerid");
            strQuery.AppendLine("WHERE sts.isfinished=1 and ");//add:Wangjh 0924
            strQuery.AppendLine(" NOT EXISTS(");
            strQuery.AppendLine("                SELECT s.containerid");
            strQuery.AppendLine("                FROM stockinfo s");
            strQuery.AppendLine("                WHERE s.containerid = sts.containerid AND s.qty <= s.inqty)");

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND sts.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(sts.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(sts.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(sts.productname) LIKE '%{0}%' OR LOWER(sts.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND sts.plannedstartdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND sts.plannedstartdate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }
            strQuery.AppendLine("ORDER BY sts.plannedstartdate DESC");

            return strQuery.ToString();
        }
        protected string GetSQL_DOld(Dictionary<string, string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT c.containername,pb.productname,p.description,c.qty,c.plannedstartdate,c.containerid,");
            strQuery.AppendLine("       mo.processno,mo.oprno,c.plannedcompletiondate,p.productid,c.uomid,s.inqty,s.StockQty ");
            strQuery.AppendLine("FROM container c");
            strQuery.AppendLine("LEFT JOIN stockinfo s ON s.containerid = c.containerid");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid");
            strQuery.AppendLine("WHERE c.status = 1 AND c.finishstate = 1");
            strQuery.AppendLine("AND c.parentcontainerid IS NULL");
            strQuery.AppendLine("AND NOT EXISTS(");
            strQuery.AppendLine("                SELECT s.containerid");
            strQuery.AppendLine("                FROM stockinfo s");
            strQuery.AppendLine("                WHERE s.containerid = c.containerid AND s.qty <= s.inqty)");

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(mo.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(c.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND c.plannedstartdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND c.plannedstartdate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }
            strQuery.AppendLine("ORDER BY c.plannedstartdate DESC");

            return strQuery.ToString();
        }
        #endregion
        #endregion

        #region 分厂二级库信息查询
        public DataTable GetFactoryStockInfo()
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(@"
                        SELECT f.*
                        FROM FactoryStock f
                        ORDER BY f.Sequence
                      ");

            DataTable dt = new DataTable();
            dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }

        #endregion

        #region 出入库原因信息查询
        public DataTable GetStockReasonInfo(Dictionary<string, string> para)
        {
            //  默认查询入库原因
            string strStockReason = "1";
            if (para.Keys.Contains("StockReason")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["StockReason"]))
                {
                    strStockReason = para["StockReason"];
                }
            }
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                    SELECT s.*
                    FROM StockReason s
                    WHERE 1 = 1
                          AND s.state = " + strStockReason + @"
                    ORDER BY s.Sequence
            ");

            DataTable dt = new DataTable();
            dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }

        #endregion


        #region 二级库主信息
        public DataTable GetFactoryStockMainInfo(Dictionary<string, object> para)
        {
            DataTable dt = new DataTable();

            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerId"))
            {
                strContainerId = (string)para["ContainerId"];
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT s.*");
            strQuery.AppendLine("FROM stockinfo s");
            strQuery.AppendLine("WHERE 1 = 1");
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND s.containerid = '" + strContainerId + "'");
            }

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty((string)para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND s.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty((string)para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(s.processno) LIKE '%{0}%'", para["ProcessNo"].ToString().ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty((string)para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(s.containername) LIKE '%{0}%'", para["ContainerName"].ToString().ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty((string)para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(s.productname) LIKE '%{0}%' OR LOWER(s.description) LIKE '%{0}%')", para["ProductName"].ToString().ToLower()));
                }
            }


            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty((string)para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND s.indate   >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty((string)para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND s.indate   <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }
            dt = OracleHelper.GetDataTable(strQuery.ToString());
            return dt;
        }

        public uMESPagingDataDTO GetMainSourceData(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = string.Empty;
            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerId"))
            {
                strContainerId = (string)para["ContainerId"];
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT s.*");
            strQuery.AppendLine("FROM stockinfo s");
            strQuery.AppendLine("WHERE 1 = 1");
            strQuery.AppendLine("AND NOT EXISTS(");
            strQuery.AppendLine("   SELECT i.containerid");
            strQuery.AppendLine("   FROM inventoryadjustmentinfo i");
            strQuery.AppendLine("   WHERE i.containerid = s.containerid AND i.result =0)");
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND s.containerid = " + strContainerId + "");
            }
            //查询未出库信息
            if (para.Keys.Contains("Out")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty((string)para["Out"]))
                {
                    strQuery.AppendLine("AND s.outqty =0");
                }
            }
            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty((string)para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND s.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty((string)para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(s.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty((string)para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(s.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty((string)para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(s.productname) LIKE '%{0}%' OR LOWER(s.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }


            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty((string)para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND s.indate   >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty((string)para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND s.indate   <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }
            strSQL = strQuery.ToString();
            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            return retR;
        }
        #endregion

        #region 获取库存调整信息
        public uMESPagingDataDTO GetStockAdjustIngData(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = string.Empty;
            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerId"))
            {
                strContainerId = (string)para["ContainerId"];
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT s.*,st.stockreasonname");
            strQuery.AppendLine("FROM InventoryAdjustmentInfo s");
            strQuery.AppendLine("LEFT JOIN stockreason st ON s.stockreasonid = st.stockreasonid");
            strQuery.AppendLine("WHERE 1 = 1 AND s.result = 0");
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND s.containerid = " + strContainerId + "");
            }

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty((string)para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND s.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty((string)para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(s.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty((string)para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(s.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty((string)para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(s.productname) LIKE '%{0}%' OR LOWER(s.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }


            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty((string)para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND s.applydate   >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty((string)para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND s.applydate   <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }
            strSQL = strQuery.ToString();
            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            return retR;
        }
        #endregion

        #endregion

        #region 质量记录
        public DataTable GetSavedQualInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();

            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            //报工单ID
            string strReportInfoID = string.Empty;
            if (para.Keys.Contains("ReportInfoID"))
            {
                strReportInfoID = (string)para["ReportInfoID"];
            }


            //是否增加查询条件
            string strIsAdd = string.Empty;
            if (para.Keys.Contains("IsAdd"))
            {
                strIsAdd = (string)para["IsAdd"];
            }

            //工艺规程ID
            string strWorkflowID = string.Empty;
            if (para.Keys.Contains("WorkflowID"))
            {
                strWorkflowID = (string)para["WorkflowID"];
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT q.*, DECODE(NVL(q.issubmit,0),0,'否',1,'是') disissubmit,sb.specname,s.specrevision,");
            strQuery.AppendLine("       e.fullname,s.unitworktime,s.setupworktime,c.qty * s.unitworktime AS totalworktime");
            strQuery.AppendLine("FROM QualityRecordInfo q");
            strQuery.AppendLine("LEFT JOIN container c ON c.containerid = q.containerid");
            strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = q.submitemployeeid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = q.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND q.containerid = '" + strContainerId + "'");
            }

            ////工序ID
            //if (strSpecID != "")
            //{
            //    strQuery.AppendLine(" AND q.SpecID = '" + strSpecID + "'");
            //}

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }

            //报工单ID
            if (strReportInfoID != "")
            {
                strQuery.AppendLine(" AND q.ReportInfoID = '" + strReportInfoID + "'");
            }

            if (strIsAdd != "")
            {
                if (strWorkflowID != "")
                {
                    strQuery.AppendLine(" AND q.workflowid = '" + strWorkflowID + "'");
                }
            }


            dt = OracleHelper.GetDataTable(strQuery.ToString());
            return dt;
        }

        /// <summary>
        /// 获取已保存的质量记录详细信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetSavedDetailQualInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();

            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //质量记录ID
            string strID = string.Empty;
            if (para.Keys.Contains("ID"))
            {
                strID = (string)para["ID"];
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT q.*,qp.*");
            strQuery.AppendLine("FROM QualityRecordInfo q");
            strQuery.AppendLine("LEFT JOIN QualityRecordProductNoInfo qp ON qp.QualityRecordInfoID = q.id");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND q.containerid = '" + strContainerId + "'");
            }

            //工序ID
            if (strSpecID != "")
            {
                strQuery.AppendLine(" AND q.SpecID = '" + strSpecID + "'");
            }

            //质量记录ID
            if (strID != "")
            {
                strQuery.AppendLine(" AND q.id = '" + strID + "'");
            }

            dt = OracleHelper.GetDataTable(strQuery.ToString());
            return dt;
        }
        /// <summary>
        /// 获取质量记录单号
        /// </summary>
        /// <param name="para"></param>
        /// <param name="intSerialNumber"></param>
        /// <returns></returns>
        public string GetQualityRecordInfoName(Dictionary<string, string> para, out int intSerialNumber)
        {
            string QualityRecordInfoName = string.Empty;

            string strPrefix = string.Empty;
            if (para.Keys.Contains("Prefix"))
            {
                strPrefix = para["Prefix"];
            }

            string strTableName = string.Empty;
            if (para.Keys.Contains("TableName"))
            {
                strTableName = para["TableName"];
            }


            string strYearAnMonth = System.DateTime.Now.Year.ToString().Substring(0, 2) + DateTime.Now.Month.ToString("00");
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT q.YearAndMonth,q.SerialNumber");
            strQuery.AppendLine("FROM  ");
            strQuery.AppendLine(strTableName + " q");
            strQuery.AppendLine("WHERE 1 = 1");
            strQuery.AppendLine("AND q.YearAndMonth ='" + strYearAnMonth + "'");
            strQuery.AppendLine("ORDER BY  q.SerialNumber DESC");

            DataTable dt = OracleHelper.GetDataTable(strQuery.ToString());

            if (dt.Rows.Count > 0)
            {
                intSerialNumber = Convert.ToInt32(dt.Rows[0]["SerialNumber"]) + 1;
                QualityRecordInfoName = strPrefix + "-" + strYearAnMonth + intSerialNumber.ToString("000");
            }
            else
            {
                intSerialNumber = 1;
                QualityRecordInfoName = strPrefix + "-" + strYearAnMonth + intSerialNumber.ToString("000");
            }
            return QualityRecordInfoName;
        }

        #region 获取报工信息
        public DataTable GetReportInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();

            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //报工单ID
            string strReportInfoID = string.Empty;
            if (para.Keys.Contains("ReportInfoID"))
            {
                strReportInfoID = (string)para["ReportInfoID"];
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT q.*");
            strQuery.AppendLine("FROM WorkReportInfo q");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND q.containerid = '" + strContainerId + "'");
            }

            //工序ID
            if (strSpecID != "")
            {
                strQuery.AppendLine(" AND q.SpecID = '" + strSpecID + "'");
            }

            //报工单ID
            if (strReportInfoID != "")
            {
                strQuery.AppendLine(" AND q.workreportinfoid = '" + strReportInfoID + "'");
            }

            dt = OracleHelper.GetDataTable(strQuery.ToString());
            return dt;
        }
        #endregion
        #endregion

        #region 完工确认

        /// <summary>
        /// 获取不合格品审理信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetRejectAppResultInfo(Dictionary<string,string>para)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT r.containerid,r.specid,r.workflowid,r.qty,ri.qty AS disqty,ri.*
                        FROM rejectappinfo r
                        LEFT JOIN rejectappproductnoinfo ri ON ri.rejectappinfoid = r.id
                        LEFT JOIN spec s ON s.specid = r.specid
                        LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid
                        WHERE 1 = 1 ");

            if (para.Keys.Contains("ContainerID") && !string.IsNullOrEmpty(para["ContainerID"]))
            {
                sb.AppendLine(" AND r.containerid = '" + para["ContainerID"] + "'");
            }

  
            if (para.Keys.Contains("SpecName") && !string.IsNullOrEmpty(para["SpecName"]))
            {
                sb.AppendLine(" AND sb.specname = '" + para["SpecName"] + "'");
            }

            //if (para.Keys.Contains("WorkflowID") && string.IsNullOrEmpty(para["WorkflowID"]))
            //{
            //    sb.AppendLine(" AND r.WorkflowID = '" + para["WorkflowID"] + "'");
            //}

            return OracleHelper.GetDataTable(sb.ToString());
        }

        /// <summary>
        /// 获取工序派工信息
        /// </summary>
        /// <param name="para"></param>
        /// <param name="strMessage"></param>
        /// <returns></returns>
        public DataTable GetSpecDispatchInfo(Dictionary<string, string> para, out int intDispatchQty)
        {
            DataTable dt = new DataTable();

            intDispatchQty = 0;
            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            //是否增加查询条件
            string strIsAdd = string.Empty;
            if (para.Keys.Contains("IsAdd"))
            {
                strIsAdd = (string)para["IsAdd"];
            }

            //工艺规程ID
            string strWorkflowID = string.Empty;
            if (para.Keys.Contains("WorkflowID"))
            {
                strWorkflowID = (string)para["WorkflowID"];
            }
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT di.qty,sb.specname,t.teamname,di.plannedcompletiondate,");
            strQuery.AppendLine("       di.id,di.workflowid,di.specid,di.dispatchdate,e.fullname, ");
            strQuery.AppendLine("       r.resourcename,e1.fullname AS deName,di.containerid,");
            strQuery.AppendLine("       di.dispatchtype,DECODE(di.dispatchtype,0,'班组派工',1,'任务指派') AS disptype,");
            strQuery.AppendLine("       s.unitworktime,s.setupworktime,c.qty * s.unitworktime AS totalworktime");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendLine("LEFT JOIN container c ON c.containerid = di.containerid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = di.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN team t ON t.teamid = di.teamid");
            strQuery.AppendLine("LEFT JOIN resourcedef r ON r.resourceid = di.resourceid");
            strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = di.dispatchemployeeid");
            strQuery.AppendLine("LEFT JOIN DispatchEmployeeInfo de ON de.dispatchinfoid = di.id");
            strQuery.AppendLine("LEFT JOIN employee e1 ON e1.employeeid = de.employeeid");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND di.containerid = '" + strContainerId + "'");
            }

            ////工序ID
            //if (strSpecID != "")
            //{
            //    strQuery.AppendLine(" AND q.SpecID = '" + strSpecID + "'");
            //}

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }

            if (strIsAdd != "")
            {
                if (strWorkflowID != "")
                {
                    strQuery.AppendLine(" AND di.workflowid = '" + strWorkflowID + "'");
                }
            }


            dt = OracleHelper.GetDataTable(strQuery.ToString());
            dt.Columns.Add("SpecNameDisp");
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string strName = dt.Rows[i]["SpecName"].ToString();
                    dt.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                }

                //获取总的工数
                DataView dv = dt.DefaultView;

                DataTable dtNew = dv.ToTable(true, "id", "qty", "dispatchtype");

                if (dtNew.Rows.Count > 0)
                {
                    string strDispType = string.Empty;
                    for (int j = 0; j < dtNew.Rows.Count; j++)
                    {
                        if (!string.IsNullOrEmpty(dtNew.Rows[j]["dispatchtype"].ToString()))
                        {
                            strDispType = dtNew.Rows[j]["dispatchtype"].ToString();
                        }
                        if (strDispType == "1")
                        {
                            if (!string.IsNullOrEmpty(dtNew.Rows[j]["qty"].ToString()))
                            {
                                intDispatchQty = intDispatchQty + Convert.ToInt32(dtNew.Rows[j]["qty"].ToString());
                            }
                        }
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 获取工序报工信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetSpecReportInfo(Dictionary<string, string> para, out int intReporyQty)
        {
            DataTable dt = new DataTable();

            intReporyQty = 0;

            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            //是否增加查询条件
            string strIsAdd = string.Empty;
            if (para.Keys.Contains("IsAdd"))
            {
                strIsAdd = (string)para["IsAdd"];
            }

            //工艺规程ID
            string strWorkflowID = string.Empty;
            if (para.Keys.Contains("WorkflowID"))
            {
                strWorkflowID = (string)para["WorkflowID"];
            }

            //报工类型
            string strReportType = string.Empty;
            if (para.Keys.Contains("ReportType"))
            {
                strReportType = (string)para["ReportType"];
            }
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT w.qty,sb.specname,t.teamname,w.workreportinfoid,w.specid,w.ReportDate,e.fullname,");
            strQuery.AppendLine("       r.resourcename,e.fullname AS deName,DECODE(w.ReportType,0,'首件',1,'其他') AS rType,");
            strQuery.AppendLine("       w.containerid,w.workflowid,s.unitworktime,s.setupworktime,c.qty * s.unitworktime AS totalworktime");
            strQuery.AppendLine("FROM WorkReportInfo w");
            strQuery.AppendLine("LEFT JOIN container c ON c.containerid = w.containerid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = w.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN DispatchInfo di ON di.id = w.DispatchInfoID");
            strQuery.AppendLine("LEFT JOIN team t ON t.teamid = di.teamid");
            strQuery.AppendLine("LEFT JOIN resourcedef r ON r.resourceid = w.resourceid");
            strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = w.ReportEmployeeID");
            strQuery.AppendLine("WHERE 1 = 1 ");

            //报工类型
            if (strReportType != "")
            {
                strQuery.AppendLine(" AND w.ReportType IN ("+strReportType+") ");
            }


            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND w.containerid = '" + strContainerId + "'");
            }

            ////工序ID
            //if (strSpecID != "")
            //{
            //    strQuery.AppendLine(" AND q.SpecID = '" + strSpecID + "'");
            //}

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }

            if (strIsAdd != "")
            {
                if (strWorkflowID != "")
                {
                    strQuery.AppendLine(" AND di.workflowid = '" + strWorkflowID + "'");
                }
            }


            dt = OracleHelper.GetDataTable(strQuery.ToString());
            dt.Columns.Add("SpecNameDisp");
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string strName = dt.Rows[i]["SpecName"].ToString();
                    dt.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                }

                //获取总的报工数
                DataView dv = dt.DefaultView;

                DataTable dtNew = dv.ToTable(true, "workreportinfoid", "qty");

                if (dtNew.Rows.Count > 0)
                {
                    for (int j = 0; j < dtNew.Rows.Count; j++)
                    {
                        if (!string.IsNullOrEmpty(dtNew.Rows[j]["qty"].ToString()))
                        {
                            intReporyQty = intReporyQty + Convert.ToInt32(dtNew.Rows[j]["qty"].ToString());
                        }
                    }
                }


            }
            return dt;
        }

        /// <summary>
        /// 获取工序检验信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetSpecCheckInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("SpecNameDisp");//检验工序
            dt.Columns.Add("reportQty");//报工数量
            dt.Columns.Add("reporter");//报工人
            dt.Columns.Add("reportdate", System.Type.GetType("System.DateTime"));//报工时间
            dt.Columns.Add("EligibilityQty");//合格数量
            dt.Columns.Add("NonsenseQty");//不合格数量
            dt.Columns.Add("checktype");//检验类型
            dt.Columns.Add("checker");//检验人
            dt.Columns.Add("checkdate", System.Type.GetType("System.DateTime"));//检验时间
            dt.Columns.Add("ScrapQty");//报废数
            dt.Columns.Add("containerid");//批次ID
            dt.Columns.Add("workflowid");//工艺规程ID
            dt.Columns.Add("specid");//工序ID
            dt.Columns.Add("unitworktime");//定额工时
            dt.Columns.Add("totalworktime");//工时总和

            //获取首检、工序检验信息
            DataTable dtCCheck = GetSpecConventionCheckInfo(para);
            if (dtCCheck.Rows.Count > 0)
            {
                for (int i = 0; i < dtCCheck.Rows.Count; i++)
                {
                    dt.Rows.Add();
                    int intAddRow = dt.Rows.Count - 1;
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["SpecNameDisp"].ToString()))
                    {
                        dt.Rows[intAddRow]["SpecNameDisp"] = dtCCheck.Rows[i]["SpecNameDisp"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["containerid"].ToString()))
                    {
                        dt.Rows[intAddRow]["containerid"] = dtCCheck.Rows[i]["containerid"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["workflowid"].ToString()))
                    {
                        dt.Rows[intAddRow]["workflowid"] = dtCCheck.Rows[i]["workflowid"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["specid"].ToString()))
                    {
                        dt.Rows[intAddRow]["specid"] = dtCCheck.Rows[i]["specid"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["rQty"].ToString()))
                    {
                        dt.Rows[intAddRow]["reportQty"] = dtCCheck.Rows[i]["rQty"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["rName"].ToString()))
                    {
                        dt.Rows[intAddRow]["reporter"] = dtCCheck.Rows[i]["rName"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["ReportDate"].ToString()))
                    {
                        dt.Rows[intAddRow]["reportdate"] = Convert.ToDateTime(dtCCheck.Rows[i]["ReportDate"].ToString());
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["EligibilityQty"].ToString()))
                    {
                        dt.Rows[intAddRow]["EligibilityQty"] = dtCCheck.Rows[i]["EligibilityQty"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["NonsenseQty"].ToString()))
                    {
                        dt.Rows[intAddRow]["NonsenseQty"] = dtCCheck.Rows[i]["NonsenseQty"].ToString();
                    }

                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["CheckType"].ToString()))
                    {
                        ////1=首件检验 0=常规检验 2=外协检验
                        //if (dtCCheck.Rows[i]["CheckType"].ToString() == "0")
                        //{
                        //    dt.Rows[intAddRow]["checktype"] = "常规检验";
                        //}
                        //if (dtCCheck.Rows[i]["CheckType"].ToString() == "1")
                        //{
                        //    dt.Rows[intAddRow]["checktype"] = "首件检验";
                        //}
                        //if (dtCCheck.Rows[i]["CheckType"].ToString() == "2")
                        //{
                        //    dt.Rows[intAddRow]["checktype"] = "外协检验";
                        //}

                        //0=首件检验 3=工序检验 4=入厂检验
                        if (dtCCheck.Rows[i]["CheckType"].ToString() == "0")
                        {
                            dt.Rows[intAddRow]["checktype"] = "首件检验";
                        }
                        if (dtCCheck.Rows[i]["CheckType"].ToString() == "3")
                        {
                            dt.Rows[intAddRow]["checktype"] = "工序检验";
                        }
                        if (dtCCheck.Rows[i]["CheckType"].ToString() == "4")
                        {
                            dt.Rows[intAddRow]["checktype"] = "入厂检验";
                        }
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["rcheckName"].ToString()))
                    {
                        dt.Rows[intAddRow]["checker"] = dtCCheck.Rows[i]["rcheckName"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["createdate"].ToString()))
                    {
                        dt.Rows[intAddRow]["checkdate"] = Convert.ToDateTime(dtCCheck.Rows[i]["createdate"].ToString());
                    }

                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["unitworktime"].ToString()))
                    {
                        dt.Rows[intAddRow]["unitworktime"] = dtCCheck.Rows[i]["unitworktime"].ToString();
                    }

                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["totalworktime"].ToString()))
                    {
                        dt.Rows[intAddRow]["totalworktime"] = dtCCheck.Rows[i]["totalworktime"].ToString();
                    }
                }
            }

            //获取工序终检信息
            DataTable dtSCheck = GetSpecLastCheckInfo(para);
            if (dtSCheck.Rows.Count > 0)
            {
                for (int i = 0; i < dtSCheck.Rows.Count; i++)
                {
                    dt.Rows.Add();
                    int intAddRow = dt.Rows.Count - 1;
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["SpecNameDisp"].ToString()))
                    {
                        dt.Rows[intAddRow]["SpecNameDisp"] = dtSCheck.Rows[i]["SpecNameDisp"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["containerid"].ToString()))
                    {
                        dt.Rows[intAddRow]["containerid"] = dtSCheck.Rows[i]["containerid"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["workflowid"].ToString()))
                    {
                        dt.Rows[intAddRow]["workflowid"] = dtSCheck.Rows[i]["workflowid"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["specid"].ToString()))
                    {
                        dt.Rows[intAddRow]["specid"] = dtSCheck.Rows[i]["specid"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["EligibilityQty"].ToString()))
                    {
                        dt.Rows[intAddRow]["EligibilityQty"] = dtSCheck.Rows[i]["EligibilityQty"].ToString();
                    }

                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["ScrapQty"].ToString()))
                    {
                        dt.Rows[intAddRow]["ScrapQty"] = dtCCheck.Rows[i]["ScrapQty"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["CheckType"].ToString()))
                    {
                        //  1 = 总检 0 = 工序终检 2 = 外协检验
                        if (dtSCheck.Rows[i]["CheckType"].ToString() == "0")
                        {
                            dt.Rows[intAddRow]["checktype"] = "工序终检";
                        }
                        if (dtSCheck.Rows[i]["CheckType"].ToString() == "1")
                        {
                            dt.Rows[intAddRow]["checktype"] = "总检";
                        }
                        if (dtSCheck.Rows[i]["CheckType"].ToString() == "2")
                        {
                            dt.Rows[intAddRow]["checktype"] = "外协检验";
                        }
                    }
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["rcheckName"].ToString()))
                    {
                        dt.Rows[intAddRow]["checker"] = dtSCheck.Rows[i]["rcheckName"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["createdate"].ToString()))
                    {
                        dt.Rows[intAddRow]["checkdate"] = dtSCheck.Rows[i]["createdate"].ToString();
                    }

                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["unitworktime"].ToString()))
                    {
                        dt.Rows[intAddRow]["unitworktime"] = dtSCheck.Rows[i]["unitworktime"].ToString();
                    }

                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["totalworktime"].ToString()))
                    {
                        dt.Rows[intAddRow]["totalworktime"] = dtSCheck.Rows[i]["totalworktime"].ToString();
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 获取首检、工序检验信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetSpecConventionCheckInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            string strConIDList = string.Empty;
            if (para.Keys.Contains("ContainerIDList"))
            {
                strConIDList = (string)para["ContainerIDList"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            //是否增加查询条件
            string strIsAdd = string.Empty;
            if (para.Keys.Contains("IsAdd"))
            {
                strIsAdd = (string)para["IsAdd"];
            }

            //工艺规程ID
            string strWorkflowID = string.Empty;
            if (para.Keys.Contains("WorkflowID"))
            {
                strWorkflowID = (string)para["WorkflowID"];
            }


            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT c.*,sb.specname,w.qty AS rQty,e.fullname AS rName,w.ReportDate,");
            strQuery.AppendLine("       e1.fullname AS rcheckName, s.unitworktime,s.setupworktime,cc.qty * s.unitworktime AS totalworktime");
            strQuery.AppendLine("FROM ConventionCheckInfo c");
            strQuery.AppendLine("LEFT JOIN container cc ON cc.containerid = c.containerid");
            strQuery.AppendLine("LEFT JOIN WorkReportInfo w ON w.workreportinfoid = c.ReportInfoID");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = c.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = w.ReportEmployeeID");
            strQuery.AppendLine("LEFT JOIN employee e1 ON e1.employeeid = c.checkemployeeid");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND c.containerid = '" + strContainerId + "'");
            }


            if (strConIDList != "")
            {
                strQuery.AppendLine(" AND c.containerid IN (" + strConIDList + ")");
            }

            //工序ID
            if (strSpecID != "")
            {
                strQuery.AppendLine(" AND c.SpecID = '" + strSpecID + "'");
            }

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }

            if (strIsAdd != "")
            {
                if (strWorkflowID != "")
                {
                    strQuery.AppendLine(" AND c.workflowid = '" + strWorkflowID + "'");
                }
            }

            dt = OracleHelper.GetDataTable(strQuery.ToString());
            dt.Columns.Add("SpecNameDisp");
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string strName = dt.Rows[i]["SpecName"].ToString();
                    dt.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                }

            }
            return dt;
        }

        /// <summary>
        /// 获取工序终检信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetSpecLastCheckInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            string strConIDList = string.Empty;
            if (para.Keys.Contains("ContainerIDList"))
            {
                strConIDList = (string)para["ContainerIDList"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            StringBuilder strQuery = new StringBuilder();

            strQuery.AppendLine("SELECT sc.*,'' AS rcheckName,'' AS createdate, s.unitworktime,s.setupworktime,c.qty * s.unitworktime AS totalworktime");
            strQuery.AppendLine("FROM SpecLastCheckInfo sc");
            strQuery.AppendLine("LEFT JOIN container c ON c.containerid = sc.containerid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = sc.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            //strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = w.ReportEmployeeID");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND sc.containerid = '" + strContainerId + "'");
            }


            if (strConIDList != "")
            {
                strQuery.AppendLine(" AND sc.containerid IN (" + strConIDList + ")");
            }
            ////工序ID
            //if (strSpecID != "")
            //{
            //    strQuery.AppendLine(" AND q.SpecID = '" + strSpecID + "'");
            //}

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }

            dt = OracleHelper.GetDataTable(strQuery.ToString());
            dt.Columns.Add("SpecNameDisp");
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string strName = dt.Rows[i]["SpecName"].ToString();
                    dt.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                }

            }
            return dt;
        }


        /// <summary>
        /// 获取不合格品审理信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetRejectAppInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            StringBuilder strQuery = new StringBuilder();

            strQuery.AppendLine("SELECT r.*,sb.specname,s.unitworktime,s.setupworktime,c.qty * s.unitworktime AS totalworktime");
            strQuery.AppendLine("FROM REJECTAPPINFO r");
            strQuery.AppendLine("LEFT JOIN Container c ON c.containerid = r.containerid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = r.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND r.containerid = '" + strContainerId + "'");
            }

            ////工序ID
            //if (strSpecID != "")
            //{
            //    strQuery.AppendLine(" AND q.SpecID = '" + strSpecID + "'");
            //}

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }

            dt = OracleHelper.GetDataTable(strQuery.ToString());
            dt.Columns.Add("SpecNameDisp");
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string strName = dt.Rows[i]["SpecName"].ToString();
                    dt.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                }

            }
            return dt;
        }


        /// <summary>
        /// 获取工序报废信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetSpecScrapInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            StringBuilder strQuery = new StringBuilder();

            strQuery.AppendLine("SELECT sp.ScrapInfoName,e.fullname,sb.specname,spi.qty,sp.QualityRecordInfoID,");
            strQuery.AppendLine("       sp.SubmitDate,l.lossreasonname,s.setupworktime,c.qty * s.unitworktime AS totalworktime");
            strQuery.AppendLine("FROM ScrapInfo sp");
            strQuery.AppendLine(" LEFT JOIN Container c ON c.containerid = sp.containerid");
            strQuery.AppendLine("LEFT JOIN ScrapProductNoInfo spi ON spi.ScrapInfoID = sp.ID");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = sp.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = sp.SubmitEmployeeID");
            strQuery.AppendLine("LEFT JOIN LossReason l ON l.lossreasonid = spi.LossReasonID");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND sp.containerid = '" + strContainerId + "'");
            }

            ////工序ID
            //if (strSpecID != "")
            //{
            //    strQuery.AppendLine(" AND q.SpecID = '" + strSpecID + "'");
            //}

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }

            dt = OracleHelper.GetDataTable(strQuery.ToString());
            dt.Columns.Add("SpecNameDisp");
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string strName = dt.Rows[i]["SpecName"].ToString();
                    dt.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                }

            }
            return dt;
        }


        /// <summary>
        /// 获取已保存的工序完工信息
        /// </summary>
        /// <param name="para"></param>
        /// <param name="strMessage"></param>
        /// <returns></returns>

        public DataTable GetContainerSpecFinishInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();

            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT q.*");
            strQuery.AppendLine("FROM ContainerSpecFinishInfo q");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = q.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND q.containerid = '" + strContainerId + "'");
            }

            if (para.ContainsKey("WorkflowID") && !string.IsNullOrWhiteSpace(para["WorkflowID"]))//add:Wangjh 20201126
            {
                strQuery.AppendFormat(" and q.workflowid='{0}' ",para["WorkflowID"]);
            }

            ////工序ID
            //if (strSpecID != "")
            //{
            //    strQuery.AppendLine(" AND q.SpecID = '" + strSpecID + "'");
            //}

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }

            dt = OracleHelper.GetDataTable(strQuery.ToString());
            return dt;
        }


        public DataTable GetSpecInfo(Dictionary<string,string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.Append(@"SELECT DISTINCT ws.workflowid,ws.workflowstepname,ws.sequence,s.specid,f.factoryname
                            FROM workflowstep ws
                            LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                            LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                            LEFT JOIN specbase sb2 ON sb2.specbaseid=s.specbaseid
                            LEFT JOIN  factory f ON f.factoryid= s.factoryid
                            WHERE 1= 1");
             strQuery.Append("AND ws.workflowid ='" +para["WorkflowID"] +"'");
            strQuery.Append("ORDER BY ws.sequence");
            DataTable dt = OracleHelper.GetDataTable(strQuery.ToString());
            return dt;
        }

        public bool RunMoveStd(Dictionary<string, string> para,ref  string message)
        {
            bool result = false;
            message = string.Empty;
            string strApiUserName=string.Empty;
            if (para.ContainsKey("ApiUserName"))
            {
                strApiUserName = para["ApiUserName"];
            }
            string strApiPassword = string.Empty;
            if (para.ContainsKey("ApiPassword"))
            {
                strApiPassword = para["ApiPassword"];
            }
            string strContainerName = string.Empty;
            if (para.ContainsKey("ContainerName"))
            {
                strContainerName = para["ContainerName"];
            }

            string strTxnDocName, strTxnName;
            strTxnDocName = "MoveStdDoc";
            strTxnName = "MoveStd";

            var m_DataList  =  new List<ClientAPIEntity>();

            var dataEntity = new ClientAPIEntity();

            dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField;
            dataEntity.ClientInputTypeEnum = InputTypeEnum.Details;
            dataEntity.ClientDataName = "Container";
            dataEntity.ClientDataValue = strContainerName;
            dataEntity.ClientOtherValue = ""; 

 
            //dataEntity = new ClientAPIEntity("ContainerName", InputTypeEnum.Details, DataTypeEnum.DataField, strContainerName, "");
            //m_DataList.Add(dataEntity);

            m_DataList.Add(dataEntity);

            var api = new CamstarClientAPI(strApiUserName, strApiPassword);
            try
            {
                result = api.RunTxnService(strTxnDocName, strTxnName, m_DataList,ref message);
                return result;
            }
            catch(Exception ex)
            {
                message = ex.Message;
                return false;
            }
          

        }

        public int UpdateFinishState(string strContainerId)
        {
            int intResult = -1;
            StringBuilder sb = new StringBuilder();
            sb.Append("UPDATE container c SET c.finishstate ='1' WHERE c.containerid='"+strContainerId+"'");
           intResult= OracleHelper.ExecuteSql(sb.ToString());
            return intResult;
        }

        #endregion

        #region 提交入库(成品库，线边库)
        /// <summary>
        /// 获取条件
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        string GetCondition(Dictionary<string, string> para) {
            StringBuilder strQuery = new StringBuilder();
            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(mo.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(c.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND c.plannedstartdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND c.plannedstartdate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }



            return strQuery.ToString();
        }

        public uMESPagingDataDTO GetMainDataInfo(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = string.Empty;

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT c.containername,pb.productname,p.description,c.qty,c.plannedstartdate,c.containerid,");
            strQuery.AppendLine("       mo.processno,mo.oprno,c.plannedcompletiondate,p.productid,c.uomid,mo.MfgOrderName,");
            strQuery.AppendLine(@" ws.workflowstepname,cf.confirmdate,'最终入库' type,1 isfinished ,ws.workflowid,s.specid,fa.factoryname,s.specid lastspecid ");
            strQuery.AppendLine("FROM container c");
            strQuery.AppendLine(@"  left join currentstatus cu on cu.currentstatusid=c.currentstatusid
                                    left join workflowstep ws on ws.workflowstepid=cu.workflowstepid
                                    left join specbase sb on sb.specbaseid=ws.specbaseid
                                    left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
                                    left join factory fa on fa.factoryid=s.factoryid
                                    left join containerspecfinishinfo cf on cf.containerid=c.containerid and cf.workflowid=ws.workflowid and cf.specid=s.specid ");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid");
            strQuery.AppendLine("WHERE c.status = 1 AND c.finishstate = 1");
            strQuery.AppendLine("AND c.parentcontainerid IS NULL");
            strQuery.AppendLine("AND NOT EXISTS(");
            strQuery.AppendLine("                SELECT s.containerid");
            strQuery.AppendLine("                FROM SubmitToStockInfo s");
            strQuery.AppendLine("                WHERE s.containerid = c.containerid and s.Isfinished=1)");

            strQuery.AppendLine(GetCondition(para));

            //车间过滤
            //if (para.Keys.Contains("FactoryID"))
            //{
            //    if (!string.IsNullOrEmpty(para["FactoryID"]))
            //    {
            //        strQuery.AppendLine(string.Format("AND s.factoryid = '{0}'", para["FactoryID"]));
            //    }
            //}

            strQuery.AppendLine(" union all ");

            strQuery.AppendLine(@" select c.containername,pb.productname,p.description,c.qty,c.plannedstartdate,c.containerid,
                                          mo.processno,mo.oprno,c.plannedcompletiondate,p.productid,c.uomid,mo.MfgOrderName,ws.workflowstepname ,cf.confirmdate
                                          , '线边库' type,0 isfinished,ws.workflowid,s.specid,fa.factoryname,s2.specid lastspecid
                                    from container c 
                                    left join currentstatus cu on cu.currentstatusid=c.currentstatusid
                                    left join workflowstep ws on ws.workflowstepid=cu.workflowstepid
                                    left join specbase sb on sb.specbaseid=ws.specbaseid
                                    left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
                                    left join factory fa on fa.factoryid=s.factoryid
                                    left join product p on p.productid=c.productid
                                    left join productbase pb on pb.productbaseid=p.productbaseid
                                    LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid
                                    --当前序派工记录
                                    left join dispatchinfo di on di.containerid=c.containerid and di.workflowstepid=ws.workflowstepid and  di.dispatchtype=0
                                    --上一个工序
                                    left join workflowstep ws2 on ws2.sequence=ws.sequence-1 and ws.workflowid=ws2.workflowid
                                    left join specbase sb2 on sb2.specbaseid=ws2.specbaseid
                                    left join spec s2 on s2.specid=nvl(sb2.revofrcdid,ws2.specid)
                                    left join containerspecfinishinfo cf on cf.containerid=c.containerid and cf.workflowid=ws.workflowid and cf.specid=s2.specid
                                    --关联线边库
                                    left join submittostockinfo st on st.containerid=c.containerid and st.workflowid=ws.workflowid and st.specid=s.specid
                                    where st.submitdate is null and c.status=1 and nvl(c.finishstate,0)!=1 and di.status=0 and di.specsequence>1 ");
            strQuery.AppendLine(@" and not exists(select 1 from synergicinfo sc where sc.isuse=0 and  sc.containerid=di.containerid and sc.workflowid=di.workflowid and sc.returnspecid=di.specid 																		
								) ");
            strQuery.AppendLine(GetCondition(para));
            //车间过滤
            //if (para.Keys.Contains("FactoryID"))
            //{
            //    if (!string.IsNullOrEmpty(para["FactoryID"]))
            //    {
            //        strQuery.AppendLine(string.Format("AND s2.factoryid = '{0}'", para["FactoryID"]));
            //    }
            //}

            strQuery.Insert(0, "select * from (");
            strQuery.AppendLine(") ORDER BY confirmdate asc");

            strSQL = strQuery.ToString();

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);

            DataTable dt = retR.DBTable;
            dt.Columns.Add("DisWorkflowStepName");
            dt.Columns.Add("LastCheckName");
            //二次处理 add:Wangjh 0927
            string strSql = @"select distinct e.fullname 
                              from conventioncheckinfo  ci
                              left join employee e on e.employeeid=ci.checkemployeeid
                              where ci.containerid='{0}' and ci.workflowid='{1}' and ci.specid='{2}'";
            foreach (DataRow row in dt.Rows) {
                string stepName = row["WorkflowStepName"].ToString();
                row["DisWorkflowStepName"] = stepName.Substring(stepName.LastIndexOf('-')+1);
                row["DisWorkflowStepName"] = row["FactoryName"].ToString()+"-" + row["DisWorkflowStepName"].ToString();
                DataTable tempDt = OracleHelper.Query(string.Format(strSql,row["containerid"].ToString(), row["workflowid"].ToString(), row["lastspecid"].ToString())).Tables[0];
                if (tempDt.Rows.Count == 0)
                    continue;
                row["LastCheckName"] = tempDt.Rows[0]["fullname"];
            }           
            retR.DBTable.AcceptChanges();
            return retR;
        }

        /// <summary>
        /// 获取提交的库房信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
       public DataTable GetSubmittostockinfo(Dictionary<string, string> para) {
            string strSql = @"select ft.factorystockname,st.* from submittostockinfo st 
                              left join factorystock ft on ft.factorystockid=st.factorystockid
                              where 1=1";
            if (para.ContainsKey("ContainerID")) {
                strSql += $" and st.ContainerID='{para["ContainerID"]}'";
            }
            if (para.ContainsKey("WorkflowID"))
            {
                strSql += $" and st.WorkflowID='{para["WorkflowID"]}'";
            }
            if (para.ContainsKey("SpecID"))
            {
                strSql += $" and st.SpecID='{para["SpecID"]}'";
            }

            return OracleHelper.GetDataTable(strSql);
        }
        #endregion


        #region 数据采集
        /// <summary>
        /// 获取检测项信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetCheckitemInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();

            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //图号ID
            string strProductID = string.Empty;
            if (para.Keys.Contains("ProductID"))
            {
                strProductID = (string)para["ProductID"];
            }

            //工艺规程ID
            string strWorkflowID = string.Empty;
            if (para.Keys.Contains("WorkflowID"))
            {
                strWorkflowID = (string)para["WorkflowID"];
            }

            //采集类型
            string strCollectType = string.Empty;
            if (para.Keys.Contains("CollectType"))
            {
                strCollectType = (string)para["CollectType"];
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT ci.*");
            strQuery.AppendLine("FROM checkiteminfo ci");
            strQuery.AppendLine("WHERE 1 = 1");

            ////跟踪卡ID
            //if (strContainerId != "")
            //{
            //    strQuery.AppendLine(" AND ci.containerid = '" + strContainerId + "'");
            //}

            //工序ID
            if (strSpecID != "")
            {
                strQuery.AppendLine(" AND ci.SpecID = '" + strSpecID + "'");
            }

            //图号ID
            if (strProductID != "")
            {
                strQuery.AppendLine(" AND ci.productid = '" + strProductID + "'");
            }

            //工艺规程ID
            if (strWorkflowID != "")
            {
                strQuery.AppendLine(" AND ci.workflowid = '" + strWorkflowID + "'");
            }

            if (strCollectType!="")
            {
                if (strCollectType=="1") //关键工序
                { strQuery.AppendLine(" AND ci.keyspec = '1'"); }
                else if (strCollectType == "2")//首件检验
                { strQuery.AppendLine(" AND ci.firstcheck = '1'"); }
                else if (strCollectType == "3")//工序检验
                { strQuery.AppendLine(" AND ci.speccheck = '1'"); }
                if (strCollectType == "4")//入场复验
                { strQuery.AppendLine(" AND ci.FactoryReCheck = '1'"); }
            }
            dt = OracleHelper.GetDataTable(strQuery.ToString());
            dt.Columns.Add("CollectType");
            if (dt.Rows.Count>0)
            {
                for (int i=0;i<dt.Rows.Count;i++)
                {
                    dt.Rows[i]["CollectType"] = strCollectType;
                }
            }
            return dt;
        }

        /// <summary>
        /// 获取已保存的数据采集信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetDataCollectInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();

            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //图号ID
            string strProductID = string.Empty;
            if (para.Keys.Contains("ProductID"))
            {
                strProductID = (string)para["ProductID"];
            }

            //工艺规程ID
            string strWorkflowID = string.Empty;
            if (para.Keys.Contains("WorkflowID"))
            {
                strWorkflowID = (string)para["WorkflowID"];
            }

            //检测项信息表ID
            string strCheckItemInfoID = string.Empty;
            if (para.Keys.Contains("CheckItemInfoID"))
            {
                strCheckItemInfoID = (string)para["CheckItemInfoID"];
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT  dc.*, dcd.productno,dcd.collectqty,dcd.checkvalue,dcd.collecttype,");
            strQuery.AppendLine("       ci.checkiteminfoname,ci.checkitem,dcd.checkitemid,dcd.SequenceNum,dcd.checkvalue1,");
            strQuery.AppendLine("       dcd.checkitemid || dcd.collecttype AS CheckItemIDandType");
            strQuery.AppendLine("FROM DataCollectInfo dc");
            strQuery.AppendLine("LEFT JOIN DataCollectDetailInfo dcd ON dc.id = dcd.datacollectinfoid");
            strQuery.AppendLine("LEFT JOIN checkiteminfo ci ON ci.checkiteminfoid = dcd.checkitemid");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND dc.containerid = '" + strContainerId + "'");
            }

            //工序ID
            if (strSpecID != "")
            {
                strQuery.AppendLine(" AND dc.SpecID = '" + strSpecID + "'");
            }

            //图号ID
            if (strProductID != "")
            {
                strQuery.AppendLine(" AND dc.productid = '" + strProductID + "'");
            }

            //工艺规程ID
            if (strWorkflowID != "")
            {
                strQuery.AppendLine(" AND dc.workflowid = '" + strWorkflowID + "'");
            }

            //检测项信息表ID
            if (strCheckItemInfoID != "")
            {
                strQuery.AppendLine(" AND dcd.checkitemid = '" + strCheckItemInfoID + "'");
            }

            if (para.ContainsKey("CollectType"))
            {
                strQuery.AppendLine(" AND dcd.collecttype = '" + para["CollectType"] + "'");
            }
            strQuery.AppendLine("ORDER BY dcd.SequenceNum");
            dt = OracleHelper.GetDataTable(strQuery.ToString());
            return dt;
        }
        #endregion


        #region 保存数据
        public bool SaveDataToDatabase(Dictionary<string,object>para,out string strMessage)
        {
            bool bReturn = true;
            strMessage = "";
            DataSet ds = new DataSet();
            if (para.Keys.Contains("dsData"))
            {
                ds = (DataSet)para["dsData"];
            }
            try
            {
                if (ds.Tables.Count > 0)
                {
                    Dictionary<string, OracleParameter[]> SQLlist = new Dictionary<string, OracleParameter[]>();
                    for (int i= 0;i<ds.Tables.Count; i++)
                    {
                       
                        //表名称
                        string strTableName = ds.Tables[i].TableName;
                        DataTable dtMain = ds.Tables[i];
                        //主键名称
                        string strKey = ds.Tables[i].Columns[0].ColumnName;
                        //主键值
                        string[] strKeyValueList = ds.Tables[i].Rows[ds.Tables[i].Rows.Count - 1][strKey].ToString().Split('㊣');
                        string strKeyValue = strKeyValueList[0];

                        string strDelSql = "DELETE FROM " + strTableName + " m" +
                                           " WHERE " + strKey + "= :" + strKey;

                        OracleParameter[] DelParam1 = new OracleParameter[1]; 
                        DelParam1[0] = new OracleParameter();
                        DelParam1[0].ParameterName = ":" + strKey;
                        DelParam1[0].OracleDbType = OracleDbType.Int32;
                        DelParam1[0].Direction = ParameterDirection.Input;
                        DelParam1[0].Value = strKeyValue;
                
                        //'添加到哈希表
                        SQLlist.Add(strDelSql, DelParam1);

                        for (int j= 0;j< dtMain.Rows.Count;j++)
                        {
                            //表列名
                            string strMainColumn = string.Empty;
                            string strMainColumnList = string.Empty;
                            for (int h= 0;h<dtMain.Columns.Count;h++)
                            {
                                string strName = dtMain.Columns[h].ColumnName.ToString();
                                if (strMainColumn.Contains("," + strName))
                                { }
                                else
                                {
                                    strMainColumn += "," + strName;
                                    if (strName.ToLower() == strKey.ToLower())
                                    { strName += j.ToString(); }
                                    strMainColumnList += "," + ":" + strName;
                                }
                            }
                            if (strMainColumn != "")
                            {
                                strMainColumn = strMainColumn.TrimStart(',');
                                strMainColumnList = strMainColumnList.TrimStart(',');

                                string strInsertSQL1 = "INSERT INTO " + strTableName + " (" + 
                                                        strMainColumn + ")" + 
                                                        " VALUES(" + 
                                                        strMainColumnList + ")";
                                OracleParameter[] param = new OracleParameter[dtMain.Columns.Count];

                                for (int k=0;k<dtMain.Columns.Count;k++)
                                {

                                    //参数名称
                                    string strParameterName = string.Empty;
                                    strParameterName = dtMain.Columns[k].ColumnName;
                                    if (strParameterName.ToLower() == strKey.ToLower())
                                    {
                                        strParameterName += j.ToString();
                                    }

                                    strParameterName = ":" + strParameterName;
                                    //行内容
                                    string strRowContent = string.Empty;
                                    if (!string.IsNullOrEmpty(dtMain.Rows[j][k].ToString()))
                                    {
                                        strRowContent = dtMain.Rows[j][k].ToString();
                                    }


                                    //保存值
                                    string strValue = string.Empty;

                                    //类型
                                    string strType = string.Empty;
                                    if (strRowContent != "")
                                    {
                                        string[] array = strRowContent.Split('㊣');
                                        strValue = array[0];
                                        strType = array[1];
                                    }

                                    if (strType == "Date")
                                    {
                                        DateTime operDate = new DateTime();
                                        if (strValue != "")
                                        {
                                            operDate = Convert.ToDateTime(strValue);
                                        }

                                        param[k] = new OracleParameter();

                                        param[k].ParameterName =  strParameterName;
                                        param[k].OracleDbType = OracleDbType.Date;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = operDate;
                                    }

                                    else if (strType == "Integer")
                                    {
                                        param[k] = new OracleParameter();
                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Int32;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = strValue;
                                    }

                                    else
                                    {
                                        param[k] = new OracleParameter();
                                        param[k].ParameterName =  strParameterName;
                                        param[k].OracleDbType = OracleDbType.Varchar2;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = strValue;
                                    }

                        

                                }
                                //添加到哈希表
                                SQLlist.Add(strInsertSQL1, param);
                            }

                        }

                    }
                    OracleHelper.ExecuteSqlTranByHash(SQLlist);
                    strMessage = "入库完成！";
                }
                else
                {
                    strMessage = "没有要保存的信息！";
                    bReturn = false;
                    return bReturn;
                }

               
            }
            catch (Exception ex)
            {
                strMessage = ex.Message;
                bReturn = false;
            }
            return bReturn;
        }

        public bool SaveDataToDatabaseNew(Dictionary<string, object> para, out string strMessage)
        {
            bool bReturn = true;
            strMessage = "";
            DataSet ds = new DataSet();
            if (para.Keys.Contains("dsData"))
            {
                ds = (DataSet)para["dsData"];
            }
            try
            {
                if (ds.Tables.Count > 0)
                {
                    Dictionary<string, OracleParameter[]> SQLlist = new Dictionary<string, OracleParameter[]>();
                    for (int i = 0; i < ds.Tables.Count; i++)
                    {

                        //表名称
                        string[] strTableNameList = ds.Tables[i].TableName.Split('㊣');
                        string strTableName = strTableNameList[0];
                        string strDelKey = strTableNameList[1];
                        DataTable dtMain = ds.Tables[i];
                        //主键名称
                        string strKey = ds.Tables[i].Columns[0].ColumnName;

                        //删除表
                        string[] strKeyValueList = ds.Tables[i].Rows[ds.Tables[i].Rows.Count - 1][strDelKey].ToString().Split('㊣');
                        string strKeyValue = strKeyValueList[0];

                        string strDelSql = "DELETE FROM " + strTableName + " m" +
                                           " WHERE " + strDelKey + "= :" + strDelKey;

                        OracleParameter[] DelParam1 = new OracleParameter[1];
                        DelParam1[0] = new OracleParameter();
                        DelParam1[0].ParameterName = ":" + strDelKey;
                        DelParam1[0].OracleDbType = OracleDbType.Varchar2;
                        DelParam1[0].Direction = ParameterDirection.Input;
                        DelParam1[0].Value = strKeyValue;

                        //'添加到哈希表
                        SQLlist.Add(strDelSql, DelParam1);

                        for (int j = 0; j < dtMain.Rows.Count-1; j++)//update:Wangjh 20201015 dtMain.Rows.Count-1->dtMain.Rows.Count
                        {
                            //表列名
                            string strMainColumn = string.Empty;
                            string strMainColumnList = string.Empty;
                            for (int h = 0; h < dtMain.Columns.Count; h++)
                            {
                                string strName = dtMain.Columns[h].ColumnName.ToString();
                                if (strMainColumn.Contains("," + strName))
                                { }
                                else
                                {
                                    strMainColumn += "," + strName;
                                    if (strName.ToLower() == strKey.ToLower())
                                    { strName += j.ToString(); }
                                    strMainColumnList += "," + ":" + strName;
                                }
                            }
                            if (strMainColumn != "")
                            {
                                strMainColumn = strMainColumn.TrimStart(',');
                                strMainColumnList = strMainColumnList.TrimStart(',');

                                string strInsertSQL1 = "INSERT INTO " + strTableName + " (" +
                                                        strMainColumn + ")" +
                                                        " VALUES(" +
                                                        strMainColumnList + ")";
                                OracleParameter[] param = new OracleParameter[dtMain.Columns.Count];

                                for (int k = 0; k < dtMain.Columns.Count; k++)
                                {

                                    //参数名称
                                    string strParameterName = string.Empty;
                                    strParameterName = dtMain.Columns[k].ColumnName;
                                    if (strParameterName.ToLower() == strKey.ToLower())
                                    {
                                        strParameterName += j.ToString();
                                    }

                                    strParameterName = ":" + strParameterName;
                                    //行内容
                                    string strRowContent = string.Empty;
                                    if (!string.IsNullOrEmpty(dtMain.Rows[j][k].ToString()))
                                    {
                                        strRowContent = dtMain.Rows[j][k].ToString();
                                    }


                                    //保存值
                                    string strValue = string.Empty;

                                    //类型
                                    string strType = string.Empty;
                                    if (strRowContent != "")
                                    {
                                        string[] array = strRowContent.Split('㊣');
                                        strValue = array[0];
                                        strType = array[1];
                                    }

                                    if (strType == "Date")
                                    {
                                        DateTime operDate = new DateTime();
                                        if (strValue != "")
                                        {
                                            operDate = Convert.ToDateTime(strValue);
                                        }

                                        param[k] = new OracleParameter();

                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Date;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = operDate;
                                    }

                                    else if (strType == "Integer")
                                    {
                                        param[k] = new OracleParameter();
                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Int32;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = strValue;
                                    }

                                    else
                                    {
                                        param[k] = new OracleParameter();
                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Varchar2;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = strValue;
                                    }



                                }
                                //添加到哈希表
                                SQLlist.Add(strInsertSQL1, param);
                            }

                        }

                    }
                    OracleHelper.ExecuteSqlTranByHash(SQLlist);
                    strMessage = "保存成功！";
                }
                else
                {
                    strMessage = "没有要保存的信息！";
                    bReturn = false;
                    return bReturn;
                }


            }
            catch (Exception ex)
            {
                strMessage = ex.Message;
                bReturn = false;
            }
            return bReturn;
        }

        //add:Wangjh 20201020
        public bool SaveDataToDatabaseNew2(Dictionary<string, object> para, out string strMessage)
        {
            bool bReturn = true;
            strMessage = "";
            DataSet ds = new DataSet();
            if (para.Keys.Contains("dsData"))
            {
                ds = (DataSet)para["dsData"];
            }
            try
            {
                if (ds.Tables.Count > 0)
                {
                    Dictionary<string, OracleParameter[]> SQLlist = new Dictionary<string, OracleParameter[]>();
                    for (int i = 0; i < ds.Tables.Count; i++)
                    {

                        //表名称
                        string[] strTableNameList = ds.Tables[i].TableName.Split('㊣');
                        string strTableName = strTableNameList[0];
                        string strDelKey = strTableNameList[1];//删除的主键
                        DataTable dtMain = ds.Tables[i];
                        //主键名称
                        string strKey = ds.Tables[i].Columns[0].ColumnName;
                                               

                        for (int j = 0; j < dtMain.Rows.Count; j++)
                        {
                            //插入前,先删除
                            string[] strKeyValueList = ds.Tables[i].Rows[j][strDelKey].ToString().Split('㊣');
                            string strKeyValue = strKeyValueList[0];//删除的值

                            string strDelSql = "DELETE FROM " + strTableName + " m" +
                                               " WHERE " + strDelKey + "= :" + strDelKey+j.ToString();

                            OracleParameter[] DelParam1 = new OracleParameter[1];
                            DelParam1[0] = new OracleParameter();
                            DelParam1[0].ParameterName = ":" + strDelKey+j.ToString();
                            DelParam1[0].OracleDbType = OracleDbType.Varchar2;
                            DelParam1[0].Direction = ParameterDirection.Input;
                            DelParam1[0].Value = strKeyValue;

                            //'添加到哈希表
                            SQLlist.Add(strDelSql, DelParam1);


                            //表列名
                            string strMainColumn = string.Empty;
                            string strMainColumnList = string.Empty;
                            for (int h = 0; h < dtMain.Columns.Count; h++)
                            {
                                string strName = dtMain.Columns[h].ColumnName.ToString();
                                if (strMainColumn.Contains("," + strName))
                                { }
                                else
                                {
                                    strMainColumn += "," + strName;
                                    if (strName.ToLower() == strKey.ToLower())
                                    { strName += j.ToString(); }
                                    strMainColumnList += "," + ":" + strName;
                                }
                            }
                            if (strMainColumn != "")
                            {
                                strMainColumn = strMainColumn.TrimStart(',');
                                strMainColumnList = strMainColumnList.TrimStart(',');

                                string strInsertSQL1 = "INSERT INTO " + strTableName + " (" +
                                                        strMainColumn + ")" +
                                                        " VALUES(" +
                                                        strMainColumnList + ")";
                                OracleParameter[] param = new OracleParameter[dtMain.Columns.Count];

                                for (int k = 0; k < dtMain.Columns.Count; k++)
                                {

                                    //参数名称
                                    string strParameterName = string.Empty;
                                    strParameterName = dtMain.Columns[k].ColumnName;
                                    if (strParameterName.ToLower() == strKey.ToLower())
                                    {
                                        strParameterName += j.ToString();
                                    }

                                    strParameterName = ":" + strParameterName;
                                    //行内容
                                    string strRowContent = string.Empty;
                                    if (!string.IsNullOrEmpty(dtMain.Rows[j][k].ToString()))
                                    {
                                        strRowContent = dtMain.Rows[j][k].ToString();
                                    }


                                    //保存值
                                    string strValue = string.Empty;

                                    //类型
                                    string strType = string.Empty;
                                    if (strRowContent != "")
                                    {
                                        string[] array = strRowContent.Split('㊣');
                                        strValue = array[0];
                                        strType = array[1];
                                    }

                                    if (strType == "Date")
                                    {
                                        DateTime operDate = new DateTime();
                                        if (strValue != "")
                                        {
                                            operDate = Convert.ToDateTime(strValue);
                                        }

                                        param[k] = new OracleParameter();

                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Date;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = operDate;
                                    }

                                    else if (strType == "Integer")
                                    {
                                        param[k] = new OracleParameter();
                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Int32;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = strValue;
                                    }

                                    else
                                    {
                                        param[k] = new OracleParameter();
                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Varchar2;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = strValue;
                                    }



                                }
                                //添加到哈希表
                                SQLlist.Add(strInsertSQL1, param);
                            }

                        }

                    }
                    OracleHelper.ExecuteSqlTranByHash(SQLlist);
                    strMessage = "保存成功！";
                }
                else
                {
                    strMessage = "没有要保存的信息！";
                    bReturn = false;
                    return bReturn;
                }


            }
            catch (Exception ex)
            {
                strMessage = ex.Message;
                bReturn = false;
            }
            return bReturn;
        }
        #endregion

        #region 更新数据
        public int UpdateDataToDatabase(DataTable dt, out string strMessage)
        {
            int iReturn = -1;
            strMessage = string.Empty;
            try
            {
                if (dt.Rows.Count > 0)
                {
                    string strTableName = dt.Rows[0][0].ToString();
                    string strKey = dt.Columns[1].ColumnName.ToString();
                    string[] strKeyValueList = dt.Rows[0][1].ToString().Split('㊣');
                    string strKeyValue = strKeyValueList[0];
                    StringBuilder strSql = new StringBuilder();
                    strSql.AppendLine("UPDATE " +strTableName);
                    strSql.AppendLine(" SET");
                    for (int i=1;i<dt.Columns.Count;i++)
                    {
                        string strColName = dt.Columns[i].ColumnName;

                        //行内容
                        string strRowContent = string.Empty;
                        if (!string.IsNullOrEmpty(dt.Rows[0][i].ToString()))
                        {
                            strRowContent = dt.Rows[0][i].ToString();
                        }


                        //保存值
                        string strValue = string.Empty;

                        //类型
                        string strType = string.Empty;
                        if (strRowContent != "")
                        {
                            string[] array = strRowContent.Split('㊣');
                            strValue = array[0];
                            strType = array[1];
                        }

                        if (strType == "Date")
                        {
                            if (i == dt.Columns.Count - 1)
                            {
                                strSql.AppendLine(string.Format(strColName + " = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", strValue));
                            }
                            else
                            {
                                strSql.AppendLine(string.Format(strColName + " = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", strValue));
                            }
                        }
                        else
                        {
                            if (i == dt.Columns.Count - 1)
                            {
                                strSql.AppendLine(string.Format(strColName + " = '{0}'", strValue));
                            }
                            else
                            {
                                strSql.AppendLine(string.Format(strColName + " = '{0}',", strValue));
                            }
                        }
                    }

                    strSql.AppendLine(string.Format("WHERE " + strKey +" ='{0}'" ,strKeyValue));
                    iReturn = OracleHelper.ExecuteSql(strSql.ToString());
                }
           
            }
            catch (Exception ex)
            {
                strMessage = ex.Message;
                iReturn = -1;
            }
            return iReturn;
        }
        #endregion

    }
}
